Ierakstu skaitīšana lielā datubāzē

[Aleksejs]

Pamēģini to pašu vaicājumu (ar Latvia) tikai bez ORDER BY daļas. Vai mainās izpildes ātrums?

[bubu]

Vai tad LIKE māk izmantot indeksus?

[Aleksejs]

Dažkārt māk:

The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. For example, the following SELECT statements use indexes:

 

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

 

In the first statement, only rows with 'Patrick' <= key_col < 'Patricl' are considered. In the second statement, only rows with 'Pat' <= key_col < 'Pau' are considered.

 

The following SELECT statements do not use indexes:

 

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

 

In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant.

 

If you use ... LIKE '%string%' and string is longer than three characters, MySQL uses the Turbo Boyer-Moore algorithm to initialize the pattern for the string and then uses this pattern to perform the search more quickly.

[nekonezinu]

Latvia 73.4716560841

Antartic 74.7929170132

 

Kverija:

 

SELECT * FROM vardi WHERE vards LIKE'$s%'

[Aleksejs]

Un ja ar mazo burtu uzraksta?

latvia

un

antartic

[nekonezinu]

Nekas nemainījās

[Aleksejs]

parādi, ko abos gadījumos iedod EXPLAIN

EXPLAIN SELECT * FROM vardi WHERE vards LIKE 'Latvia%';
EXPLAIN SELECT * FROM vardi WHERE vards LIKE 'music%';

[nekonezinu]

Latvia:

Array ( [id] => 1 [select_type] => SIMPLE [table] => vardi [type] => range [possible_keys] => vards_in [key] => vards_in [key_len] => 765 [ref] => [rows] => 1 [Extra] => Using where )

 

music:

Array ( [id] => 1 [select_type] => SIMPLE [table] => vardi [type] => range [possible_keys] => vards_in [key] => vards_in [key_len] => 765 [ref] => [rows] => 5 [Extra] => Using where )

 

dvdrip:

Array ( [id] => 1 [select_type] => SIMPLE [table] => vardi [type] => range [possible_keys] => vards_in [key] => vards_in [key_len] => 765 [ref] => [rows] => 2 [Extra] => Using where )

[Klez]

nekonezinu, tev phpMyAdmin nav pa rokai ?

veel buutu labi ja paraadiitu tabulas struktuuru ...

vai vismaz sho: SHOW INDEX FROM vardi

[Aleksejs]

Hmm, un cik ātri šitāda izpildās:

SELECT * FROM vardi WHERE MATCH (vards) AGAINST ('Latvia')

?

[nekonezinu]

$aax = mysql_query("SELECT * FROM vardi WHERE MATCH (vards) AGAINST ('Latvia')");

while ($col = mysql_fetch_assoc($aax)){

echo $col['skaits'];

}

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

[andrisp]

nekonezinu, a tev taada tabula "vardi" vispaar ir ? :)

[nekonezinu]

Protams ka ir!

[andrisp]

mysql_error()

[nekonezinu]

Can't find FULLTEXT index matching the column list