Databases
StackOverflow2013
Postsdbo
POMySQL "Sending data" horribly slow
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL "Sending data" horribly slow
    primarykey
    data
    text
    <p>I have a modest-sized table, 277k records at the moment, which I am trying to do a <code>FULLTEXT</code> search on. The search seems to be very quick until it gets to the Sending data phase.</p> <p><strong>The Table:</strong></p> <pre><code>CREATE TABLE `sqinquiries_inquiry` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ts` datetime NOT NULL, `names` longtext NOT NULL, `emails` longtext NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `sqinquiries_inquiry_search` (`names`,`emails`) ) ENGINE=MyISAM AUTO_INCREMENT=305560 DEFAULT CHARSET=latin1 </code></pre> <p><strong>The Query:</strong></p> <pre><code>SELECT * FROM `sqinquiries_inquiry` WHERE ( MATCH (`sqinquiries_inquiry`.`names`) AGAINST ('smith' IN BOOLEAN MODE) OR MATCH (`sqinquiries_inquiry`.`emails`) AGAINST ('smith' IN BOOLEAN MODE) ) ORDER BY `sqinquiries_inquiry`.`id` DESC LIMIT 100 </code></pre> <p><strong>The Profile:</strong> (I snipped out seemingly useless info)</p> <pre><code>+-------------------------+----------+ | Status | Duration | +-------------------------+----------+ | preparing | 0.000014 | | FULLTEXT initialization | 0.000015 | | executing | 0.000004 | | Sorting result | 0.000008 | | Sending data | 2.247934 | | end | 0.000011 | | query end | 0.000003 | +-------------------------+----------+ </code></pre> <p>The <code>DESCRIBE</code> looks great, a simple one liner: <strong>The Describe:</strong></p> <pre><code>id: 1 select_type: SIMPLE table: sqinquiries_inquiry type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 100 Extra: Using where </code></pre> <p>So what I don't understand is where the 2.25 seconds of Sending data is coming from? I'm seeing similar performance in Python and in the console <code>mysql</code> app, both connecting to <code>localhost</code>.</p> <p><strong>Updates:</strong></p> <ul> <li>Per comment requesting the average row size, it is: 53.8485</li> <li>Per comment, here is the <code>DESCRIBE</code> above.</li> </ul>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOMG Ponies
    1. USJack M.
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL "Sending data" horribly slow
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL "Sending data" horribly slow
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL "Sending data" horribly slow
      1. This table or related slice is empty.
      1. VTUpMod
    1. COhow big is the amount of data you're retrieving? if you don't know, then look at the table statistics and tell us the average row size.
      singulars
      1. POMySQL "Sending data" horribly slow
      1. USlongneck
    2. COWasn't sure how to figure out the average row size, so I threw the output of this up there: `SELECT AVG(LENGTH(names) + LENGTH(emails) + LENGTH(id) + LENGTH(ts)) as avg_length from sqinquiries_inquiry`. If there is a better way, please let me know.
      singulars
      1. POMySQL "Sending data" horribly slow
      1. USJack M.
    3. COThe problem is that your `FULLTEXT KEY` is not used. Could you please post the `DESCRIBE`?
      singulars
      1. POMySQL "Sending data" horribly slow
      1. USQuassnoi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload